import time
from Basic.read_excel import OpenpyxlExcel
from Basic.base_log import log
from Basic.api_mysql import *

db_namne = 'automated_testing' # 数据库名称
table_project = 'test_project' # 项目名称 excel标签页
table_module = 'test_module'   # 用例模块
table_element = 'test_element' # 用例元素

sql_project = '''CREATE TABLE IF NOT EXISTS %s.%s (
  `id` INT NOT NULL AUTO_INCREMENT,
  `project_name` VARCHAR(100) NOT NULL,
  `create_time` DATETIME(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
'''% (db_namne,table_project)

sql_module = '''CREATE TABLE IF NOT EXISTS %s.%s (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `module_name` VARCHAR(100) NOT NULL,
  `project_id` INT(10),
  PRIMARY KEY (`id`),
  CONSTRAINT %s FOREIGN KEY (`project_id`) REFERENCES %s(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
'''% (db_namne,table_module,'fk_project_id',table_project)

sql_element = '''CREATE TABLE IF NOT EXISTS %s.%s (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `case_id` VARCHAR(20) NOT NULL,
  `precondition` VARCHAR(100) DEFAULT NULL,
  `operate_step` VARCHAR(100) NOT NULL,
  `element_operate` VARCHAR(200) NOT NULL,
  `element_method` VARCHAR(100) NOT NULL,
  `elemernt_action` VARCHAR(100) NOT NULL,
  `element_sendkey` VARCHAR(500) DEFAULT NULL  ,
  `time_sleep` VARCHAR(10) DEFAULT NULL ,
  `bool_execute` CHAR(2) DEFAULT '是'  ,
  `assert_name` VARCHAR(100) DEFAULT NULL,
  `assert_ele` VARCHAR(100) DEFAULT NULL,
  `assert_ele_method` VARCHAR(20) DEFAULT NULL,
  `assert_ele_action` VARCHAR(20) DEFAULT NULL,
  `assert_method` VARCHAR(20) DEFAULT NULL,
  `expect_results` VARCHAR(100) DEFAULT NULL,
  `module_id` INT(10),
  PRIMARY KEY (`id`),
  CONSTRAINT %s FOREIGN KEY (`module_id`) REFERENCES %s(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
'''% (db_namne,table_element,'fk_module_id',table_module)

# # 创建数据库，表格
# up_pydb = pymysqlHandle('localhost', 'root', 'root', 3306, None)
# rt1 = up_pydb.creat_database(db_namne)
# rt2 = up_pydb.create_table(sql_element)
# print(rt1,rt2)

#数据库连接
# pydb = pymysqlHandle('localhost', 'root', 'root', 3306, 'djangoserver')

def excel_to_mysql(filename, sheetname):
    """将excel用例导入mysql"""
    #连接数据库，表格
    up_pydb = pymysqlHandle('localhost', 'root', 'root', 3306)
    #创建数据库
    rt1 = up_pydb.creat_database(db_namne)
    rt2 = up_pydb.create_table(sql_project)
    rt3 = up_pydb.create_table(sql_module)
    rt4 = up_pydb.create_table(sql_element)
    up_pydb.closeDb()
    if (rt2 or rt3 or rt4) == None  :
        log.error('-- -- --> 创建表格失败')
        return None
    log.info('-- -- --> 数据库:%s,表:%s,%s,%s 创建成功： %s, %s' %(db_namne,table_project,table_module,table_element,rt1, rt2))
    #连接数据库
    log.info('-- -- --> 连接数据库： %s, ' % (db_namne))
    pydb = pymysqlHandle('localhost', 'root', 'root', 3306, db_namne)
    log.info('-- -- --> 获取excel 每行的用例参数，返回一个字典：caseDict ' )
    # 获取所有用例模块及其所在行数范围，返回值为字典如 {'LOGIN':[2, 3, 4, 5, 6, 7, 8, 9, 10, 11]}
    AllCaseMoudleDict = OpenpyxlExcel().get_caseMoud_AllLine(filename, sheetname)
    log.info('-- -- --> AllCaseMoudleDict:%s'%AllCaseMoudleDict)
    # 遍历所有用例模块及其所在行数范围的字典
    for caseMoudleName in AllCaseMoudleDict:
        log.info('-- %s --> 用例开始导入mysql' % (caseMoudleName))
        log.info('-- -- --> caseMoudleName:%s' % caseMoudleName)
        # 根据模块名称得到该模块所在行数的范围 列表
        caseMoudleList = AllCaseMoudleDict[caseMoudleName]
        log.info('-- -- --> caseMoudleList:%s' % caseMoudleList)
        # 遍历该模块下的行数，得到每行用例的执行元素: 如caseMoudleList:[2, 3, 4, 5]
        for i in range(len(caseMoudleList)):
            excel = OpenpyxlExcel()
            # sheet = excel.get_sheet(filename, sheetname)
            caseDict = excel.get_row_cells(filename, sheetname, caseMoudleList[i])
            for case in caseDict:
                if caseDict[case] == None or caseDict[case] == '':
                    caseDict[case]=''
            log.info('-- -- --> caseDict：%s' % (caseDict))
            # 将元素导入数据库
            result = insertDb_element(pydb,sheetname, caseDict)  # 此步骤如果执行出错，后续需进一步处理。。。。。
            # return result


'''如果数据库中 项目 不存在，则新增该项目'''
def if_insert_project(pydb,sheetname):
    log.info('-- -- --> 开始新增项目：%s'%sheetname)
    create_time = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
    # 新增项目
    result = pydb.insertDB("INSERT INTO %s(project_name,create_time) SELECT '%s','%s' \
                            FROM DUAL WHERE NOT EXISTS (SELECT project_name FROM %s WHERE project_name='%s')" % (
        table_project,sheetname,create_time, table_project,sheetname))
    log.info('-- -- --> 新增项目：%s ,result:%s ' % (sheetname, result))
    return result

'''如果数据库中 模块 不存在，则新增该模块'''
def if_insert_module(pydb,sheetname, moduleName):
    log.info('-- -- --> 开始新增模块:%s'%moduleName)
    # 根据项目名称查找项目ID
    proj_id = pydb.selectDb("SELECT id FROM %s WHERE project_name='%s'" % (table_project, sheetname))
    # log.info('-- -- --> proj_id: %s' % proj_id)
    if proj_id == None:
        log.error('-- -- --> proj_id ==%s' % proj_id)
        return None
    # 新增模块
    result = pydb.insertDB("INSERT INTO %s(module_name,project_id) SELECT '%s',%s \
         FROM DUAL WHERE NOT EXISTS (SELECT module_name FROM %s WHERE module_name='%s')" % (table_module, moduleName, proj_id,table_module, moduleName))
    log.info('-- -- --> 新增模块：%s  成功,result:%s' % (moduleName,result))
    return result


def insertDb_element(pydb,sheetname, caseDict):
    moduleName = caseDict['用例模块']
    #新增项目
    result = if_insert_project(pydb, sheetname)
    if result == None:
        log.error('-- -- --> if_insert_project ==%s' % result)
        return result
    # 新增模块
    result = if_insert_module(pydb, sheetname, moduleName)
    if result == None:
        log.error('-- -- --> if_insert_module ==%s' % result)
        return None
    # 根据项目名称查找项目ID
    proj_id = pydb.selectDb("SELECT id FROM %s WHERE project_name='%s'" % (table_project, sheetname))
    if proj_id == None:
        log.error('-- -- --> proj_id ==%s' % proj_id)
        return False
    log.info('-- -- --> 根据项目名称：%s ,查找项目ID： proj_id:%s '%(sheetname,proj_id))
    # 根据项目ID和模块名称查找模块ID
    mod_id = pydb.selectDb(
        "SELECT id FROM %s WHERE module_name='%s' AND project_id='%s' " % (table_module,moduleName,proj_id))
    if mod_id == None:
        log.error('-- -- -->  mod_id ==%s' % (mod_id))
        return None
    log.error('-- -- --> 根据项目ID和模块名称:%s 查找模块ID： mod_id ==%s ' % (moduleName, mod_id))
    str=caseDict['操作元素']
    log.error("-- -- --> caseDict['操作元素']=%s ， %s " % (str,type(str)))

    ele_sql = "INSERT INTO %s( \
        case_id,precondition,operate_step,element_operate,element_method,elemernt_action,element_sendkey,time_sleep,bool_execute,\
        assert_name,assert_ele,assert_ele_method,assert_ele_action,assert_method,expect_results,module_id) value \
        ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s') " % (table_element,
        caseDict['编号'],caseDict['前提条件'], caseDict['操作步骤'], caseDict['操作元素'], caseDict['元素定位方式'], \
        caseDict['元素动作'], caseDict['输入数据'], caseDict['延时'], caseDict['是否执行'], \
        caseDict['断言元素'], caseDict['断言元素方法'], caseDict['断言元素动作'], \
        caseDict['断言方法'], caseDict['预期结果'], caseDict['断言结果'], mod_id)
    # log.info('-- -- --> sql: %s '%ele_sql)
    result = pydb.insertDB(ele_sql)
    log.info('-- -- --> 新增用例：%s , result：%s ' % (caseDict['操作步骤'], result))
    return result

# if_insert_project('海拔4')
# if_insert_module('凤凰','haluo')

filename = r'D:\Workspace_Selenium\SeleniumUiniTest UiFramework 20210316\Conf\6101_Element.xlsx'
sheetname = '携程'
excel_to_mysql(filename,sheetname)

#新增用例
# '''INSERT INTO test_element(case_id,precondition,operate_step,element_operate,element_method,elemernt_action,element_sendkey,time_sleep,bool_execute,assert_name,assert_ele,assert_ele_method,assert_ele_action,assert_method,expect_results,module_id) value ('1',null,'打开网站','https://www.baidu.com','url','url','null',null,'是',null,null,null,null,null,null,'1')'''
